package mimipost.database;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.LinkedList;
import java.util.List;

import minipost.post.model.Post;

public class PostSql extends Database {

	public PostSql() {
		super();
	}
	
	/**
	 * 获取所有文章列表
	 * @return 文章列表
	 * @throws SQLException
	 */
	public List<Post> getAllPosts() throws SQLException {
		PreparedStatement statement = 
				connection.prepareStatement("SELECT * FROM posts");
		ResultSet result = statement.executeQuery();
		
		return getPostList(result);
	}
	
	public List<Post> getAllPosts(int index, int count) throws SQLException {
		PreparedStatement statement = 
				connection.prepareStatement("SELECT * FROM posts LIMIT " + ((index - 1) * count) + "," + count);
		ResultSet result = statement.executeQuery();
		
		return getPostList(result);
	}
	
	/**
	 * 按照文章类型查询
	 * @param type 文章类型
	 * @return 文章列表
	 * @throws SQLException
	 */
	public List<Post> getPostsByType(String type) throws SQLException {
		PreparedStatement statement = 
				connection.prepareStatement("SELECT * FROM posts where post_type = ?");
		statement.setString(1, type);
		ResultSet result = statement.executeQuery();

		return getPostList(result);
	}
	
	/**
	 * 按照文章编号查询
	 * @param id 文章编号
	 * @return 文章，未查到为null
	 * @throws SQLException
	 */
	public Post getPostById(int id) throws SQLException {
		PreparedStatement statement = 
				connection.prepareStatement("SELECT * FROM posts where id = ?");
		statement.setInt(1, id);
		ResultSet result = statement.executeQuery();
		
		List<Post> posts = getPostList(result);
		if (posts != null && posts.size() > 0) {
			return posts.get(0);
		}
		return null;
	}
	
	/**
	 * 获取结果集的文章列表
	 * @param result 查询结果集
	 * @return 文章列表
	 * @throws SQLException
	 */
	private List<Post> getPostList(ResultSet result) throws SQLException {
		List<Post> postList = new LinkedList<Post>();
		while (result.next()) {
			Post post = new Post();
			post.setId(result.getInt("id"));
			post.setPostAuthor(result.getString("post_author"));
			post.setPostTitle(result.getString("post_title"));
			post.setPostContent(result.getString("post_content"));
			post.setPostType(result.getString("post_type"));			
			post.setPostDate(result.getTimestamp("post_date"));
			post.setPostModified(result.getTimestamp("post_modified"));

			postList.add(post);
		}
		
		return postList.size() > 0 ? postList : null;
	}
	
	/**
	 * 插入新文章
	 * @param post 新文章
	 * @throws SQLException
	 */
	public void insert(Post post) throws SQLException {
		PreparedStatement statement = 
				connection.prepareStatement("INSERT INTO posts (post_author, post_title, post_content, post_type, post_date, post_modified) " 
						+ "VALUES(?, ?, ?, ?, ?, ?)");
		statement.setString(1, post.getPostAuthor());
		statement.setString(2, post.getPostTitle());
		statement.setString(3, post.getPostContent());
		statement.setString(4, post.getPostType());
		Timestamp current = new Timestamp(System.currentTimeMillis());
		statement.setTimestamp(5, current);
		statement.setTimestamp(6, current);
		
		statement.executeUpdate();
		statement.close();
		//connection.commit();
	}
	
	/**
	 * 修改文章
	 * @param post 要修改的文章
	 * @throws SQLException
	 */
	public void update(Post post) throws SQLException {
		PreparedStatement statement = 
				connection.prepareStatement("UPDATE posts SET post_title = ?, post_type = ?, post_author = ?, post_content = ?, post_modified = ? WHERE id = ?");
		statement.setString(1, post.getPostTitle());
		statement.setString(2, post.getPostType());
		statement.setString(3, post.getPostAuthor());
		statement.setString(4, post.getPostContent());
		Timestamp current = new Timestamp(System.currentTimeMillis());
		statement.setTimestamp(5, current);		
		statement.setInt(6, post.getId());
		
		statement.executeUpdate();
		statement.close();
		//connection.commit();
	}
	
	/**
	 * 按照文章编号删除
	 * @param id 文章编号
	 * @throws SQLException
	 */
	public void deleteById(int id) throws SQLException {
		PreparedStatement statement = 
				connection.prepareStatement("DELETE FROM posts WHERE id = ?");
		statement.setInt(1, id);
		
		statement.executeUpdate();
		statement.close();
		//connection.commit();
	}
	
	public String getCount() throws Exception {
		String result = null;
		
		PreparedStatement statement = 
				connection.prepareStatement("SELECT COUNT(*) as total FROM posts");
		ResultSet data = statement.executeQuery();
		if (data.next()) {
			result = data.getString("total");
		}
		return result;
	}
	
	private static final long serialVersionUID = 6567229600270109542L;

}
